package com.zf.fastexcel.utils.handler;

import cn.idev.excel.ExcelWriter;
import cn.idev.excel.FastExcel;
import cn.idev.excel.write.handler.SheetWriteHandler;
import cn.idev.excel.write.metadata.WriteSheet;
import cn.idev.excel.write.metadata.holder.WriteSheetHolder;
import cn.idev.excel.write.metadata.holder.WriteWorkbookHolder;
import org.apache.commons.collections4.CollectionUtils;
import org.apache.commons.collections4.MapUtils;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddressList;
import org.apache.poi.ss.util.CellReference;

import java.util.*;

/**
 * 自定义单元格下拉值列表
 * 限制：此种方式下，每个单元格的下拉选项的总长度不能超过255个字符，否则报错
 */
public class CustomCellDropdownSheetWriteHandler implements SheetWriteHandler {


    /**
     * 下拉数据：columnIndex -> 下拉选项列表
     */
    private final Map<Integer, List<String>> columnDropdownMap = new LinkedHashMap<>();

    /**
     * 表头行数
     */
    private int headerRowNumber = 0;

    /**
     * 给多少行设置下拉，表头除外，默认设置1000行
     */
    private int dropdownRowNumber = 1000;

    /**
     * <pre>
     * 是否引用其他的表格内容作为下拉值来源，默认false。
     * 当单个单元格的下拉选项的字符总长度超过255个字符时，请设置为true，否则会超长报错。
     * 为true时，会把下拉选项的内容写入到一个隐藏的sheet中，然后使用该sheet作为下拉值来源
     * </pre>
     */
    private boolean referenceOtherSheetAsSource = false;


    public CustomCellDropdownSheetWriteHandler(Map<Integer, List<String>> columnDropdownMap, int headerRowNumber, int dropdownRowNumber, boolean referenceOtherSheetAsSource) {
        for (Map.Entry<Integer, List<String>> entry : Optional.ofNullable(columnDropdownMap).orElse(Collections.emptyMap()).entrySet()) {
            if (CollectionUtils.isEmpty(entry.getValue())) {
                continue;
            }
            this.columnDropdownMap.put(entry.getKey(), entry.getValue());
        }
        if (headerRowNumber >= 0) {
            this.headerRowNumber = headerRowNumber;
        }
        if (dropdownRowNumber > 0) {
            this.dropdownRowNumber = dropdownRowNumber;
        }
        this.referenceOtherSheetAsSource = referenceOtherSheetAsSource;
    }

    @Override
    public void afterSheetCreate(WriteWorkbookHolder writeWorkbookHolder, WriteSheetHolder writeSheetHolder) {
        if (MapUtils.isEmpty(columnDropdownMap)) {
            return;
        }
        if (referenceOtherSheetAsSource) {
            cellDropdownReferenceOtherSheet(writeWorkbookHolder, writeSheetHolder);
        } else {
            cellDropdownInCurrentSheet(writeWorkbookHolder, writeSheetHolder);
        }
    }

    /**
     * 不使用其他sheet，直接创建下拉选项，适用于单个单元格的下拉选项的总长度不超过255个字符的情况
     */
    public void cellDropdownInCurrentSheet(WriteWorkbookHolder writeWorkbookHolder, WriteSheetHolder writeSheetHolder) {
        for (Map.Entry<Integer, List<String>> entry : columnDropdownMap.entrySet()) {
            // 列
            Integer columnIndex = entry.getKey();
            // 下拉选项的内容
            String[] options = entry.getValue().toArray(new String[0]);

            // 创建数据验证帮助器
            DataValidationHelper helper = writeSheetHolder.getSheet().getDataValidationHelper();
            // 创建下拉列表约束
            DataValidationConstraint constraint = helper.createExplicitListConstraint(options);
            // 设置作用范围，行号从零开始计数，设置从“表头的下一行 开始到 指定的最大行”
            CellRangeAddressList cellRangeAddressList = new CellRangeAddressList(headerRowNumber, headerRowNumber + dropdownRowNumber - 1, columnIndex, columnIndex);
            // 创建数据验证规则
            DataValidation dataValidation = helper.createValidation(constraint, cellRangeAddressList);
            // 添加验证规则到工作表
            writeSheetHolder.getSheet().addValidationData(dataValidation);
        }
    }

    /**
     * 使用其他sheet作为下拉选项来源，适用于单个单元格的下拉选项的总长度超过255个字符的情况
     */
    public void cellDropdownReferenceOtherSheet(WriteWorkbookHolder writeWorkbookHolder, WriteSheetHolder writeSheetHolder) {
        Workbook workbook = writeWorkbookHolder.getWorkbook();
        // 创建一个隐藏的sheet，存放下拉选项
        String hiddenSheetName = "OptionsHiddenBelongToSheet_" + writeSheetHolder.getSheet().getSheetName();
        Sheet hiddenSheet = workbook.createSheet(hiddenSheetName);
        // 隐藏这个sheet
        workbook.setSheetHidden(workbook.getSheetIndex(hiddenSheet), true);

        // 向隐藏的sheet中写入下拉选项
        for (Map.Entry<Integer, List<String>> entry : columnDropdownMap.entrySet()) {
            Integer columnIndex = entry.getKey();
            List<String> optionList = entry.getValue();
            for (int i = 0; i < optionList.size(); i++) {
                Row row = hiddenSheet.getRow(i);
                if (Objects.isNull(row)) {
                    row = hiddenSheet.createRow(i);
                }
                Cell cell = row.createCell(columnIndex);
                cell.setCellValue(optionList.get(i));
            }
        }

        // 给当前sheet设置下拉
        Sheet sheet = writeSheetHolder.getSheet();
        for (Map.Entry<Integer, List<String>> entry : columnDropdownMap.entrySet()) {
            // 列
            Integer columnIndex = entry.getKey();
            // 下拉选项的内容
            List<String> optionList = entry.getValue();
            // 列引用标识，A, AB
            String columAlphabetCode = CellReference.convertNumToColString(columnIndex);

            // 使用 命名范围，来避免“此文件可能已经被移动、重命名或删除。此链接存在于数据验证规则中。”警告
            Name namedRange = workbook.createName();
            String nrName = hiddenSheetName + "Ref" + columAlphabetCode;
            // 命名范围名称
            namedRange.setNameName(nrName);
            // 命名范围引用区域
            String formula = String.format("%s!$%s$%d:$%s$%d", hiddenSheetName, columAlphabetCode, 1, columAlphabetCode, optionList.size());
            namedRange.setRefersToFormula(formula);

            // 创建数据验证帮助器
            DataValidationHelper helper = sheet.getDataValidationHelper();
            // 使用公式引用隐藏 sheet 中的数据范围，使用命名范围的名称
            DataValidationConstraint constraint = helper.createFormulaListConstraint(nrName);
            // 设置作用范围，行号从零开始计数，设置从“表头的下一行 开始到 指定的最大行”
            CellRangeAddressList regions = new CellRangeAddressList(headerRowNumber, headerRowNumber + dropdownRowNumber - 1, columnIndex, columnIndex);
            // 创建数据验证规则
            DataValidation validation = helper.createValidation(constraint, regions);
            // 添加验证规则到工作表
            sheet.addValidationData(validation);
        }

    }


    public static void main(String[] args) {
        List<List<String>> headers = Arrays.asList(
                Collections.singletonList("头1"),
                Collections.singletonList("头2"),
                Collections.singletonList("头3")
        );

        List<String> dropdown1 = new ArrayList<>();
        for (int i = 0; i < 100; i++) {
            dropdown1.add("下拉" + i);
        }

        List<String> dropdown2 = Arrays.asList("下拉5", "下拉6");
        HashMap<Integer, List<String>> dropdownMap1 = new HashMap<>();
        dropdownMap1.put(0, dropdown1);
        dropdownMap1.put(2, dropdown2);

        List<String> dropdown3 = new ArrayList<>();
        for (int i = 0; i < 100; i++) {
            dropdown3.add("选项" + i);
        }

        List<String> dropdown4 = Arrays.asList("选项5", "选项6");
        HashMap<Integer, List<String>> dropdownMap2 = new HashMap<>();
        dropdownMap2.put(0, dropdown3);
        dropdownMap2.put(2, dropdown4);



        try (ExcelWriter excelWriter = FastExcel.write("D:\\test.xlsx").build()) {
            WriteSheet writeSheet1 = FastExcel.writerSheet(0)
                    .head(headers)
                    .registerWriteHandler(new CustomCellDropdownSheetWriteHandler(dropdownMap1, 1, 20, true))
                    .build();


            WriteSheet writeSheet2 = FastExcel.writerSheet(1)
                    .head(headers)
                    .registerWriteHandler(new CustomCellDropdownSheetWriteHandler(dropdownMap2, 1, 5, true))
                    .registerWriteHandler(new MoveHiddenSheetToLastWriteHandler())
                    .build();
            excelWriter.write(Collections.emptyList(), writeSheet1);
            excelWriter.write(Collections.emptyList(), writeSheet2);
        }
    }


    // public static void main(String[] args) {
    //     AnalysisEventListener<Map<Integer, String>> listener = new AnalysisEventListener<>() {
    //
    //         @Override
    //         public void invoke(Map<Integer, String> data, AnalysisContext context) {
    //             System.out.println(data);
    //         }
    //
    //         @Override
    //         public void doAfterAllAnalysed(AnalysisContext context) {
    //
    //         }
    //     };
    //
    //     FastExcel.read("D:\\test.xlsx")
    //             .sheet(1)
    //             .registerReadListener(listener)
    //             .doRead();
    // }


}
